package com.example.keshe.Dao.impl;

import com.example.keshe.Dao.InventoryDao;
import com.example.keshe.Inventory;
import com.example.keshe.util.DruidUtil;
import javafx.application.Platform;
import javafx.scene.control.Alert;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.CompletableFuture;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

/**
 * InventoryDaoImpl 实现 InventoryDao 接口，用于对库存数据进行数据库操作。
 *
 * @param <T> 泛型类型，通常为 Inventory 类型
 */
public class InventoryDaoImpl<T> implements InventoryDao<T> {
    private Connection conn; // 数据库连接对象
    private PreparedStatement pstmt; // 数据库的操作对象
    private ExecutorService executorService = Executors.newFixedThreadPool(5); // 线程池，用于异步操作

    /**
     * 构造一个InventoryDaoImpl实例，指定数据库连接对象。
     *
     * @param conn 数据库连接对象
     */
    public InventoryDaoImpl(Connection conn) {this.conn = conn;}

    /**
     * 分页查询库存数据。
     *
     * @param pageIndex 页索引
     * @param pageSize  每页记录数
     * @return List 包含查询结果的列表
     * @throws SQLException 如果数据库操作出现异常
     */
    @Override
    public List<T> selectByPage(int pageIndex, int pageSize) throws SQLException {
        int offset = (pageIndex - 1) * pageSize; // 计算偏移量
        String sql = "SELECT * FROM inventory LIMIT ? OFFSET ?"; // SQL查询语句，限制返回数量并设置偏移量
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, pageSize); // 设置返回的记录数量
            pstmt.setInt(2, offset); // 设置查询偏移量
            try (ResultSet rs = pstmt.executeQuery()) {
                List<Inventory> inventoryList = new ArrayList<>();
                while (rs.next()) {
                    // 从结果集中获取每一行的数据并创建相应的Inventory对象
                    inventoryList.add(new Inventory(
                            rs.getString("inventoryId"),
                            rs.getString("clothingId"),
                            rs.getString("purchasePrice"),
                            rs.getString("purchaseQuantity"),
                            rs.getString("totalAmount"),
                            rs.getString("existingStock"),
                            rs.getString("inventoryDate")
                    ));
                }
                return (List<T>) inventoryList; // 返回查询结果集合
            }
        }
    }

    /**
     * 获取库存表中的总记录数。
     *
     * @return int 总记录数
     * @throws SQLException 如果数据库操作出现异常
     */
    @Override
    public int getTotalRecords() throws SQLException {
        String sql = "SELECT COUNT(*) FROM inventory"; // SQL查询语句，统计总记录数
        try (PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {
            if (rs.next()) {
                return rs.getInt(1); // 返回总记录数
            }
        }
        return 0; // 若没有记录，返回0
    }

    /**
     * 显示信息提示框。
     *
     * @param title   提示框标题
     * @param content 提示框内容
     */
    private void showInformationAlert(String title, String content) {
        showAlert(title, content, Alert.AlertType.INFORMATION);
    }

    /**
     * 显示错误提示框。
     *
     * @param title   提示框标题
     * @param content 提示框内容
     */
    private void showErrorAlert(String title, String content) {
        showAlert(title, content, Alert.AlertType.ERROR);
    }

    /**
     * 弹出提示框。
     *
     * @param title     提示框标题
     * @param content   提示框内容
     * @param alertType 提示框类型
     */
    private void showAlert(String title, String content, Alert.AlertType alertType) {
        // 使用Platform.runLater确保在JavaFX应用程序线程上运行，避免UI线程阻塞
        Platform.runLater(() -> {
            Alert alert = new Alert(alertType);
            alert.setTitle(title);
            alert.setHeaderText(null);
            alert.setContentText(content);
            alert.showAndWait();
        });
    }

    /**
     * 添加 isValidInput 方法。
     *
     * @param inventory 库存对象
     * @return boolean 表示输入是否有效
     */
    private boolean isValidInput(T inventory) {
        if (inventory instanceof Inventory) {
            Inventory validInventory = (Inventory) inventory;
            // 检查Inventory对象中的关键字段是否有效（非空且不为null）
            return isValidString(validInventory.getInventoryId()) &&
                    isValidString(validInventory.getClothingId()) &&
                    isValidString(validInventory.getPurchasePrice()) &&
                    isValidString(validInventory.getPurchaseQuantity()) &&
                    isValidString(validInventory.getInventoryDate());
        }
        return false;
    }

    /**
     * 辅助方法，检查字符串是否有效（非空且不为null）。
     *
     * @param value 待检查的字符串
     * @return boolean 表示字符串是否有效
     */
    private boolean isValidString(String value) {
        return value != null && !value.isEmpty();
    }

    /**
     * 检查服装ID是否存在。
     *
     * @param conn      数据库连接对象
     * @param clothingId 服装ID
     * @return boolean 表示服装ID是否存在
     * @throws SQLException 如果数据库操作出现异常
     */
    private boolean isClothingIdValid(Connection conn, String clothingId) throws SQLException {
        String sql = "SELECT COUNT(*) FROM clothing WHERE clothingid = ?";// SQL查询语句，统计clothing表中是否存在指定的clothingId
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, clothingId);
            try (ResultSet resultSet = pstmt.executeQuery()) {
                if (resultSet.next()) {
                    return resultSet.getInt(1) > 0;// 如果计数大于0，说明clothingId存在
                }
            }
        }
        return false;
    }

    /**
     * 检查库存表中 inventoryId 是否唯一。
     *
     * @param conn       数据库连接对象
     * @param inventoryId 库存ID
     * @return boolean 表示 inventoryId 是否唯一
     * @throws SQLException 如果数据库操作出现异常
     */
    private boolean isInventoryIdUnique(Connection conn, String inventoryId) throws SQLException {
        String sql = "SELECT COUNT(*) FROM inventory WHERE inventoryid = ?"; // 查询库存表，检查是否存在相应的inventoryId记录
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, inventoryId);
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    int count = rs.getInt(1);
                    return count == 0;// 如果记录数为0，说明inventoryId是唯一的
                }
            }
        }
        return false;
    }

    /**
     * 更新 Clothing 表中对应 clothingId 的库存。
     *
     * @param conn         数据库连接对象
     * @param clothingId   服装ID
     * @param existingStock 库存量
     * @throws SQLException 如果发生数据库错误
     */
    private void updateClothingStock(Connection conn, String clothingId, String existingStock) throws SQLException {
        String sql = "UPDATE clothing SET quantity = ? WHERE clothingid = ?";// SQL更新语句，将clothing表中对应clothingId的库存更新为指定值
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, Integer.parseInt(existingStock)); // 将字符串转换为INT类型
            pstmt.setString(2, clothingId);
            pstmt.executeUpdate();
        }
    }

    /**
     * 插入库存记录到 inventory 表。
     *
     * @param conn          数据库连接对象
     * @param validInventory 有效的库存记录
     * @param totalAmount   总金额
     * @param existingStock 当前库存
     * @throws SQLException 如果发生数据库错误
     */
    private void insertInventoryRecord(Connection conn, Inventory validInventory, BigDecimal totalAmount, String existingStock) throws SQLException {
        String sql = "INSERT INTO inventory (inventoryid, clothingid, purchaseprice, purchasequantity, totalamount, existingstock, inventorydate) VALUES (?, ?, ?, ?, ?, ?, ?)";// SQL插入语句，将库存记录插入inventory表
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, validInventory.getInventoryId());
            pstmt.setString(2, validInventory.getClothingId());
            pstmt.setString(3, validInventory.getPurchasePrice());
            pstmt.setString(4, validInventory.getPurchaseQuantity());
            pstmt.setBigDecimal(5, totalAmount);
            pstmt.setString(6, existingStock); // 使用用户输入的库存值
            pstmt.setString(7, validInventory.getInventoryDate());
            pstmt.executeUpdate();
        }
    }

    /**
     * 查询 clothing 表获取当前库存。
     *
     * @param conn      数据库连接对象
     * @param clothingId 服装ID
     * @return 当前库存
     * @throws SQLException 如果发生数据库错误
     */
    private BigDecimal getCurrentStockFromClothing(Connection conn, String clothingId) throws SQLException {

        String sql = "SELECT quantity FROM clothing WHERE clothingid = ?";// SQL查询语句，从clothing表中获取指定clothingId的当前库存
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, clothingId);
            try (ResultSet resultSet = pstmt.executeQuery()) {
                if (resultSet.next()) {return new BigDecimal(resultSet.getInt("quantity"));}
            }
        }
        return BigDecimal.ZERO;
    }

    /**
     * 异步添加库存记录。
     *
     * @param inventory 库存记录
     * @return 异步操作的 CompletableFuture
     */
    @Override
    public CompletableFuture<Void> addInventoryAsync(T inventory) {
        return CompletableFuture.runAsync(() -> {
            try (Connection conn = DruidUtil.getConnection()) {
                if (isValidInput(inventory)) {
                    Inventory validInventory = (Inventory) inventory;
                    if (isClothingIdValid(conn, validInventory.getClothingId())) {
                        if (isInventoryIdUnique(conn, validInventory.getInventoryId())) {
                            BigDecimal purchasePrice = new BigDecimal(validInventory.getPurchasePrice());
                            BigDecimal purchaseQuantity = new BigDecimal(validInventory.getPurchaseQuantity());
                            BigDecimal totalAmount = purchasePrice.multiply(purchaseQuantity);
                            BigDecimal currentStock = getCurrentStockFromClothing(conn, validInventory.getClothingId());// 查询clothing表获取当前库存
                            BigDecimal newStock = currentStock.add(purchaseQuantity); // 计算新的库存值
                            validInventory.setExistingStock(newStock.toString());
                            updateClothingStock(conn, validInventory.getClothingId(), newStock.toString());// 更新clothing表中的库存
                            insertInventoryRecord(conn, validInventory, totalAmount, newStock.toString());// 插入库存记录
                            showInformationAlert("成功", "库存记录已添加");
                        } else {showErrorAlert("错误", "库存ID重复，无法完成库存记录添加。");}
                    } else {showErrorAlert("错误", "服装ID不存在，无法完成库存记录添加。");}
                } else {showErrorAlert("错误", "库存记录不完整，无法完成添加。");}
            } catch (SQLException e) {
                e.printStackTrace();
                showErrorAlert("错误", "发生数据库错误，无法完成操作。");
            }
        }, executorService);
    }

    /**
     * 异步删除库存记录。
     *
     * @param inventory 库存记录
     * @return 异步操作的 CompletableFuture
     */
    @Override
    public CompletableFuture<Void> deleteInventoryAsync(T inventory) {
        return CompletableFuture.runAsync(() -> {
            try (Connection conn = DruidUtil.getConnection()) {
                if (inventory instanceof Inventory) {
                    Inventory inventoryToDelete = (Inventory) inventory;
                    StringBuilder sqlBuilder = new StringBuilder("DELETE FROM inventory WHERE 1=1");// 构建SQL语句
                    // 添加过滤条件（模糊删除，仅处理非空的条件）
                    if (isValidString(inventoryToDelete.getInventoryId())) {sqlBuilder.append(" AND inventoryid LIKE '%").append(inventoryToDelete.getInventoryId()).append("%'");}
                    if (isValidString(inventoryToDelete.getClothingId())) {sqlBuilder.append(" AND clothingid LIKE '%").append(inventoryToDelete.getClothingId()).append("%'");}
                    if (isValidString(inventoryToDelete.getPurchasePrice())) {sqlBuilder.append(" AND purchaseprice LIKE '%").append(inventoryToDelete.getPurchasePrice()).append("%'");}
                    if (isValidString(inventoryToDelete.getPurchaseQuantity())) {sqlBuilder.append(" AND purchasequantity LIKE '%").append(inventoryToDelete.getPurchaseQuantity()).append("%'");}
                    if (isValidString(inventoryToDelete.getTotalAmount())) {sqlBuilder.append(" AND totalamount LIKE '%").append(inventoryToDelete.getTotalAmount()).append("%'");}
                    if (isValidString(inventoryToDelete.getExistingStock())) {sqlBuilder.append(" AND existingstock LIKE '%").append(inventoryToDelete.getExistingStock()).append("%'");}
                    if (isValidString(inventoryToDelete.getInventoryDate())) {sqlBuilder.append(" AND inventorydate LIKE '%").append(inventoryToDelete.getInventoryDate()).append("%'");}
                    // 创建PreparedStatement
                    try (PreparedStatement pstmt = conn.prepareStatement(sqlBuilder.toString())) {
                        int rowsAffected = pstmt.executeUpdate();// 执行删除操作
                        // 显示删除结果提示
                        Platform.runLater(() -> {
                            if (rowsAffected > 0) {showInformationAlert("成功", rowsAffected + " 条有关的库存记录已删除");
                            } else {
                                showErrorAlert("警告", "未删除任何记录，请确保输入正确的删除条件。");
                            }
                        });
                    }
                }
            } catch (SQLException e) {
                e.printStackTrace();
                // 显示错误提示
                Platform.runLater(() -> {showErrorAlert("错误", "发生数据库错误，无法完成操作。");});
            }
        }, executorService);
    }

    /**
     * 异步计算单个进货总金额。
     *
     * @param clothingId 服装ID
     * @param startTime  开始日期
     * @param endTime    结束日期
     * @return 异步操作的 CompletableFuture，计算结果以字符串形式返回
     */
    @Override
    public CompletableFuture<String> calculateMoney(String clothingId, String startTime, String endTime) {
        // 确保输入完整性
        if (clothingId.isEmpty() || startTime.isEmpty() || endTime.isEmpty()) {
            CompletableFuture<String> incompleteInputFuture = new CompletableFuture<>();
            incompleteInputFuture.completeExceptionally(new IllegalArgumentException("请提供完整的服装ID、开始日期和结束日期。"));
            return incompleteInputFuture;
        }
        return CompletableFuture.supplyAsync(() -> {
            try (Connection conn = DruidUtil.getConnection()) {
                if (isClothingIdValid(conn, clothingId)) {
                    String sql = "SELECT SUM(totalamount) FROM inventory WHERE clothingid = ? AND inventorydate BETWEEN ? AND ?";
                    try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
                        pstmt.setString(1, clothingId);
                        pstmt.setString(2, startTime);
                        pstmt.setString(3, endTime);
                        try (ResultSet resultSet = pstmt.executeQuery()) {
                            if (resultSet.next()) {
                                double totalAmount = resultSet.getDouble(1);
                                if (totalAmount > 0) {
                                    return String.format("在时间段 %s 到 %s，服装 %s 的进货总金额为 %.2f 元", startTime, endTime, clothingId, totalAmount);// 返回格式化后的字符串
                                } else {Platform.runLater(() -> showAlert("提示", "在此时间内没有进货记录", Alert.AlertType.INFORMATION));}
                            } else {Platform.runLater(() -> showAlert("提示", "没有此服装的进货记录", Alert.AlertType.INFORMATION));}
                        }
                    }
                } else {Platform.runLater(() -> showAlert("错误", "服装ID不存在，无法计算进货金额。", Alert.AlertType.ERROR));}
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return null; // 返回默认值
        }, executorService);
    }

    /**
     * 异步计算在给定时间范围内所有库存项的总金额。
     *
     * @param startTime 计算开始时间
     * @param endTime   计算结束时间
     * @return 一个CompletableFuture，包含计算结果的字符串。如果计算成功，返回格式化的总金额字符串；如果没有符合条件的库存项记录，返回提示信息；如果发生异常，返回 null。
     */
    @Override
    public CompletableFuture<String> calculateAllTotalAmount(String startTime, String endTime) {
        return CompletableFuture.supplyAsync(() -> {
            try (Connection conn = DruidUtil.getConnection()) {
                String sql = "SELECT SUM(totalamount) FROM inventory WHERE inventorydate BETWEEN ? AND ?";
                try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
                    pstmt.setString(1, startTime);
                    pstmt.setString(2, endTime);
                    try (ResultSet resultSet = pstmt.executeQuery()) {
                        if (resultSet.next()) {
                            double totalAmount = resultSet.getDouble(1);
                            if (totalAmount > 0) {
                                return String.format("在时间段 %s 到 %s，所有库存项的总金额为 %.2f 元", startTime, endTime, totalAmount);// 返回格式化后的字符串
                            } else {
                                Platform.runLater(() -> showAlert("提示", "在此时间内没有库存记录", Alert.AlertType.INFORMATION));
                            }
                        } else {
                            Platform.runLater(() -> showAlert("提示", "没有库存记录", Alert.AlertType.INFORMATION));
                        }
                    }
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return null; // 返回默认值
        }, executorService);
    }
}
